package com.ruoyi.statisticalCenter.service.impl;

import cn.hutool.core.bean.BeanUtil;
import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.PageHelper;
import com.ruoyi.common.core.domain.R;
import com.ruoyi.common.domain.vo.InitTableVo;
import com.ruoyi.common.domain.vo.TableVo;
import com.ruoyi.common.service.TableService;
import com.ruoyi.common.utils.GetDateStringUtils;
import com.ruoyi.hqfinance.domain.HqNetworkBillDetail;
import com.ruoyi.hqfinance.mapper.HqNetworkBillDetailMapper;
import com.ruoyi.logistic.domain.PageDate;
import com.ruoyi.logistic.domain.PageInfo;
import com.ruoyi.logistic.domain.ScOutPortWarehouseGrossProfitSheetDetail;
import com.ruoyi.logistic.mapper.ScOutPortWarehouseGrossProfitSheetDetailMapper;
import com.ruoyi.qualityControl.domain.ScOutBoundSendSheet;
import com.ruoyi.statisticalCenter.domain.ScOutPortWarehouseGrossProfitSheet;
import com.ruoyi.statisticalCenter.domain.ScOutboundSendClientSet;
import com.ruoyi.statisticalCenter.domain.vo.*;
import com.ruoyi.statisticalCenter.mapper.ScOutBoundSendSheetMapper;
import com.ruoyi.statisticalCenter.mapper.ScOutPortWarehouseGrossProfitMapper;
import com.ruoyi.statisticalCenter.mapper.ScOutPortWarehouseGrossProfitSheetMapper;
import com.ruoyi.statisticalCenter.mapper.ScOutboundSendClientSetMapper;
import com.ruoyi.statisticalCenter.service.ScOutPortWarehouseGrossProfitSheetService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.math.MathContext;
import java.math.RoundingMode;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.YearMonth;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

import static java.math.BigDecimal.*;

@Service
public class ScOutPortWarehouseGrossProfitSheetServiceImpl implements ScOutPortWarehouseGrossProfitSheetService {
    @Autowired
    private ScOutPortWarehouseGrossProfitSheetMapper scOutPortWarehouseGrossProfitSheetMapper;
    @Autowired
    private HqNetworkBillDetailMapper hqNetworkBillDetailMapper;
    @Autowired
    private TableService tableService;
    @Autowired
    private ScOutPortWarehouseGrossProfitMapper scOutPortWarehouseGrossProfitMapper;
    @Autowired
    private ScOutPortWarehouseGrossProfitSheetDetailMapper detailMapper;
    MathContext mc = new MathContext(3, RoundingMode.HALF_UP); // 设置精度为3位，采用四舍五入模式

    @Autowired
    private ScOutBoundSendSheetMapper scOutBoundSendSheetMapper;
    @Autowired
    private ScOutboundSendClientSetMapper scOutboundSendClientSetMapper;


    @Override
    public R collect(ScOutPortWarehouseGrossProfitSheetVo vo) {
        //获取配置参数
        ScOutPortWareDispositionVo scOutPortWareDispositionVo = scOutPortWarehouseGrossProfitSheetMapper.selectDisposition();
        BeanUtils.copyProperties(scOutPortWareDispositionVo, vo);
        //获取全部中转费
        String sum = scOutPortWarehouseGrossProfitSheetMapper.collectSum(vo);
        vo.setSum(sum);
        //获取中转费信息
        PageHelper.startPage(vo.getPageDate());
        List<ScOutPortWarehouseGrossProfitSheet> sheets = scOutPortWarehouseGrossProfitSheetMapper.collectTransfer(vo);
        //获取包仓内信息
        List<ScOutPortWarehouseGrossProfitSheet> scOutPortWarehouseGrossProfitSheets = scOutPortWarehouseGrossProfitSheetMapper.collectIW(vo);
        //获取包仓外信息
        List<ScOutPortWarehouseGrossProfitSheet> owList = scOutPortWarehouseGrossProfitSheetMapper.collectOW(vo);
        //获取6kg以上信息
//        List<ScOutPortWarehouseGrossProfitSheet> overList = scOutPortWarehouseGrossProfitSheetMapper.collectOverSix(vo);
        //获取偏远信息
        List<ScOutPortWarehouseGrossProfitSheet> remote = scOutPortWarehouseGrossProfitSheetMapper.collectRemote(vo);
        //获取偏远信息
//        List<ScOutPortWarehouseGrossProfitSheet> ln = scOutPortWarehouseGrossProfitSheetMapper.collectLN(vo);

        for (ScOutPortWarehouseGrossProfitSheet sheet : sheets) {

            for (ScOutPortWarehouseGrossProfitSheet scOutPortWarehouseGrossProfitSheet : scOutPortWarehouseGrossProfitSheets) {
                if (sheet.getContractArea().equals(scOutPortWarehouseGrossProfitSheet.getContractArea())) {
                    sheet.setIwWeight(scOutPortWarehouseGrossProfitSheet.getIwWeight());
                    sheet.setIwStopLossWeight(scOutPortWarehouseGrossProfitSheet.getIwStopLossWeight());
                    sheet.setDailyAssessmentWeight(scOutPortWarehouseGrossProfitSheet.getDailyAssessmentWeight());
                    sheet.setIwDifference(scOutPortWarehouseGrossProfitSheet.getIwDifference());
                    sheet.setIwFee(scOutPortWarehouseGrossProfitSheet.getIwFee());
                    sheet.setIwParcelCount(scOutPortWarehouseGrossProfitSheet.getIwParcelCount());
                    sheet.setIwAverageWeight(scOutPortWarehouseGrossProfitSheet.getIwAverageWeight());
                    sheet.setIwAssessmentAverageWeight(scOutPortWarehouseGrossProfitSheet.getIwAssessmentAverageWeight());
                    sheet.setIwPerParcelFee(scOutPortWarehouseGrossProfitSheet.getIwPerParcelFee());
                    sheet.setIwPerKgFee(scOutPortWarehouseGrossProfitSheet.getIwPerKgFee());
                }
            }
            for (ScOutPortWarehouseGrossProfitSheet scOutPortWarehouseGrossProfitSheet : owList) {
                if (sheet.getContractArea().equals(scOutPortWarehouseGrossProfitSheet.getContractArea())) {
                    sheet.setOwWeight(scOutPortWarehouseGrossProfitSheet.getOwWeight());
                    sheet.setOwFee(scOutPortWarehouseGrossProfitSheet.getOwFee());
                    sheet.setOwParcelCount(scOutPortWarehouseGrossProfitSheet.getOwParcelCount());
                    sheet.setOwPerParcelFee(scOutPortWarehouseGrossProfitSheet.getOwPerParcelFee());
                    sheet.setOwPerKgFee(scOutPortWarehouseGrossProfitSheet.getOwPerKgFee());
                }
            }
//            for (ScOutPortWarehouseGrossProfitSheet scOutPortWarehouseGrossProfitSheet : overList) {
//                if (sheet.getContractArea().equals(scOutPortWarehouseGrossProfitSheet.getContractArea())) {
//                    sheet.setOverSixAmount(scOutPortWarehouseGrossProfitSheet.getOverSixAmount());
//                    sheet.setOverSixWeight(scOutPortWarehouseGrossProfitSheet.getOverSixWeight());
//                    sheet.setOverSixPerKgFee(scOutPortWarehouseGrossProfitSheet.getOverSixPerKgFee());
//                    sheet.setOverSixParcelCount(scOutPortWarehouseGrossProfitSheet.getOverSixParcelCount());
//                    sheet.setOverSixPerParcelFee(scOutPortWarehouseGrossProfitSheet.getOverSixPerParcelFee());
//                    sheet.setOverSixProportion(BigDecimal.valueOf(scOutPortWarehouseGrossProfitSheet.getOverSixParcelCount() / (sheet.getOwParcelCount() + sheet.getIwParcelCount())));
//                }
//            }
            for (ScOutPortWarehouseGrossProfitSheet scOutPortWarehouseGrossProfitSheet : remote) {
                if (sheet.getContractArea().equals(scOutPortWarehouseGrossProfitSheet.getContractArea())) {
                    sheet.setRemoteAreaAmount(scOutPortWarehouseGrossProfitSheet.getRemoteAreaAmount());
                    sheet.setRemoteAreaPerKgFee(scOutPortWarehouseGrossProfitSheet.getRemoteAreaPerKgFee());
                    sheet.setRemoteAreaWeight(scOutPortWarehouseGrossProfitSheet.getRemoteAreaWeight());
                    sheet.setRemoteAreaParcelCount(scOutPortWarehouseGrossProfitSheet.getRemoteAreaParcelCount());
                    sheet.setRemoteAreaPerParcelFee(scOutPortWarehouseGrossProfitSheet.getRemoteAreaPerParcelFee());
                    if (sheet.getOwParcelCount() + sheet.getIwParcelCount() == 0) {
                        sheet.setRemoteAreaProportion(new BigDecimal(0));
                    } else {
                        sheet.setRemoteAreaProportion(valueOf(scOutPortWarehouseGrossProfitSheet.getRemoteAreaParcelCount() / (sheet.getOwParcelCount() + sheet.getIwParcelCount())));
                    }
                    sheet.setOverSixAmount(BigDecimal.valueOf(Math.abs(scOutPortWarehouseGrossProfitSheet.getOverSixAmount().doubleValue())));
                    sheet.setOverSixWeight(scOutPortWarehouseGrossProfitSheet.getOverSixWeight());
                    sheet.setOverSixPerKgFee(scOutPortWarehouseGrossProfitSheet.getOverSixPerKgFee());
                    sheet.setOverSixParcelCount(scOutPortWarehouseGrossProfitSheet.getOverSixParcelCount());
                    sheet.setOverSixPerParcelFee(scOutPortWarehouseGrossProfitSheet.getOverSixPerParcelFee());
                    if (sheet.getOwParcelCount() + sheet.getIwParcelCount() == 0) {
                        sheet.setOverSixProportion(valueOf(0));
                    } else {
                        sheet.setOverSixProportion(valueOf(scOutPortWarehouseGrossProfitSheet.getOverSixParcelCount() / (sheet.getOwParcelCount() + sheet.getIwParcelCount())));
                    }
                    sheet.setLnAmount(scOutPortWarehouseGrossProfitSheet.getLnAmount());
                    sheet.setLnWeight(scOutPortWarehouseGrossProfitSheet.getLnWeight());
                    sheet.setLnPerKgFee(scOutPortWarehouseGrossProfitSheet.getLnPerKgFee());
                    sheet.setLnParcelCount(scOutPortWarehouseGrossProfitSheet.getLnParcelCount());
                    sheet.setLnPerParcelFee(scOutPortWarehouseGrossProfitSheet.getLnPerParcelFee());
                    if (sheet.getOwParcelCount() + sheet.getIwParcelCount() == 0) {
                        sheet.setLnProportion(valueOf(0));
                    } else {
                        sheet.setLnProportion(valueOf(scOutPortWarehouseGrossProfitSheet.getLnParcelCount() / (sheet.getOwParcelCount() + sheet.getIwParcelCount())));
                    }
                    if (sheet.getLnProportion() == null) {
                        sheet.setLnProportion(new BigDecimal(0));
                    }
                    if (sheet.getRemoteAreaProportion() == null) {
                        sheet.setRemoteAreaProportion(new BigDecimal(0));
                    }
                }
            }
//            for (ScOutPortWarehouseGrossProfitSheet scOutPortWarehouseGrossProfitSheet : ln) {
//                if (sheet.getContractArea().equals(scOutPortWarehouseGrossProfitSheet.getContractArea())) {
//                    sheet.setLnAmount(scOutPortWarehouseGrossProfitSheet.getLnAmount());
//                    sheet.setLnWeight(scOutPortWarehouseGrossProfitSheet.getLnWeight());
//                    sheet.setLnPerKgFee(scOutPortWarehouseGrossProfitSheet.getLnPerKgFee());
//                    sheet.setLnParcelCount(scOutPortWarehouseGrossProfitSheet.getLnParcelCount());
//                    sheet.setLnPerParcelFee(scOutPortWarehouseGrossProfitSheet.getLnPerParcelFee());
//                    sheet.setLnProportion(BigDecimal.valueOf(scOutPortWarehouseGrossProfitSheet.getLnParcelCount() / (sheet.getOwParcelCount() + sheet.getIwParcelCount())));
//                    if (sheet.getLnProportion() == null) {
//                        sheet.setLnProportion(new BigDecimal(0));
//                    }
//                }
//            }
            sheet.setTotalWeight(sheet.getIwWeight().add(sheet.getOwWeight()));
            //sheet.setActualTransferCost(sheet.getTransferFee().subtract(sheet.getFixedReturn()));//修改前

            sheet.setActualTransferCost(sheet.getActualCharges().subtract(sheet.getFixedFee()));
            sheet.setReceivableWarehouseFee(scOutPortWareDispositionVo.getStorageCharge());
            sheet.setActualOWFee(sheet.getOwFee());
            sheet.setWarehouseFeeProfitAndLoss(scOutPortWareDispositionVo.getStorageCharge().subtract(sheet.getAccumulatedAmount()));
            sheet.setTransferFeeDeficit(sheet.getActualOWFee().subtract(sheet.getActualTransferCost()));
            sheet.setTotalProfitAndLoss(sheet.getTransferFeeDeficit().add(sheet.getWarehouseFeeProfitAndLoss()));


        }

        JSONObject jsonObject = new JSONObject();
        jsonObject.put("list", PageInfo.info(sheets));
        jsonObject.put("fields", getTableVo(vo.getNetworkLevel()));

        if (sheets == null || sheets.size() == 0) {
            return R.fail("暂无数据");
        } else {
            return R.ok(jsonObject);
        }
    }

    @Override
    public List<ScOutPortWarehouseGrossProfitSheet> collectList(ScOutPortWarehouseGrossProfitSheetVo vo, OutputStream outputStream) {
        ScOutPortWareDispositionVo scOutPortWareDispositionVo = scOutPortWarehouseGrossProfitSheetMapper.selectDisposition();
        BeanUtils.copyProperties(scOutPortWareDispositionVo, vo);
        //PageHelper.startPage(vo.getPageDate());
        List<ScOutPortWarehouseGrossProfitSheet> list = detailMapper.selectByAll(vo);
        if (list == null || list.size() == 0) {
            list = collectNewCache(vo);
        }
        if (!vo.getNetworkLevel().equals("0")) {
            vo.setNetworkLevel("0");
            List<ScOutPortWarehouseGrossProfitSheet> scOutPortWarehouseGrossProfitSheets = detailMapper.selectByAll(vo);
            BigDecimal transferFeeSum = new BigDecimal(0.000);
            BigDecimal fixedReturnSum = new BigDecimal(0.000);
            BigDecimal transferExamine = new BigDecimal(0.000);
            for (ScOutPortWarehouseGrossProfitSheet scOutPortWarehouseGrossProfitSheet : scOutPortWarehouseGrossProfitSheets) {
                transferFeeSum = transferFeeSum.add(scOutPortWarehouseGrossProfitSheet.getTransferFee());
                fixedReturnSum = fixedReturnSum.add(scOutPortWarehouseGrossProfitSheet.getFixedReturn());
                transferExamine = scOutPortWarehouseGrossProfitSheet.getTransferExamine();
            }
            BigDecimal rate = fixedReturnSum.divide(transferFeeSum, 10, ROUND_HALF_UP);
            for (ScOutPortWarehouseGrossProfitSheet scOutPortWarehouseGrossProfitSheet : list) {
                if (scOutPortWarehouseGrossProfitSheet.getTransferFee() != null) {
                    scOutPortWarehouseGrossProfitSheet.setFixedReturn(rate.multiply(scOutPortWarehouseGrossProfitSheet.getTransferFee()).setScale(2, RoundingMode.HALF_UP));
                    scOutPortWarehouseGrossProfitSheet.setActualCharges(scOutPortWarehouseGrossProfitSheet.getTransferFee().add(scOutPortWarehouseGrossProfitSheet.getFixedReturn()));
                    scOutPortWarehouseGrossProfitSheet.setActualTransferCost(scOutPortWarehouseGrossProfitSheet.getActualCharges().subtract(scOutPortWarehouseGrossProfitSheet.getFixedFee()));
                }
                if (scOutPortWarehouseGrossProfitSheet.getContractArea().equals("辽宁盘锦公司")){
                    scOutPortWarehouseGrossProfitSheet.setTransferExamine(transferExamine);
                }
                //包仓内日考核重量
                scOutPortWarehouseGrossProfitSheet.setDailyAssessmentWeight(new BigDecimal(0));
                scOutPortWarehouseGrossProfitSheet.setIwDifference(new BigDecimal(0));
            }

        }
        for (ScOutPortWarehouseGrossProfitSheet scOutPortWarehouseGrossProfitSheet : list) {
            scOutPortWarehouseGrossProfitSheet.setReceivableWarehouseFee(scOutPortWarehouseGrossProfitSheet.getReceivableWarehouseFee().setScale(0,RoundingMode.HALF_UP));
            scOutPortWarehouseGrossProfitSheet.setTransferFeeDeficit(scOutPortWarehouseGrossProfitSheet.getOwFee().subtract(scOutPortWarehouseGrossProfitSheet.getActualTransferCost()));
            if (scOutPortWarehouseGrossProfitSheet.getTransferFeeDeficit() == null) {
                scOutPortWarehouseGrossProfitSheet.setTransferFeeDeficit(new BigDecimal(0));
            }
            if (scOutPortWarehouseGrossProfitSheet.getTransferExamine() == null) {
                scOutPortWarehouseGrossProfitSheet.setTransferExamine(new BigDecimal(0));
            }
            scOutPortWarehouseGrossProfitSheet.setTotalProfitAndLoss(scOutPortWarehouseGrossProfitSheet.getWarehouseFeeProfitAndLoss()
                    .add(scOutPortWarehouseGrossProfitSheet.getTransferFeeDeficit().subtract(scOutPortWarehouseGrossProfitSheet.getTransferExamine())));
        }
        /*//获取配置参数
        ScOutPortWareDispositionVo scOutPortWareDispositionVo = scOutPortWarehouseGrossProfitSheetMapper.selectDisposition();
        BeanUtils.copyProperties(scOutPortWareDispositionVo, vo);
        //获取全部中转费
        String sum = scOutPortWarehouseGrossProfitSheetMapper.collectSum(vo);
        vo.setSum(sum);
        //获取中转费信息
        PageHelper.startPage(vo.getPageDate());
        List<ScOutPortWarehouseGrossProfitSheet> sheets = scOutPortWarehouseGrossProfitSheetMapper.collectTransfer(vo);
        //获取包仓内信息
        List<ScOutPortWarehouseGrossProfitSheet> scOutPortWarehouseGrossProfitSheets = scOutPortWarehouseGrossProfitSheetMapper.collectIW(vo);
        //获取包仓外信息
        List<ScOutPortWarehouseGrossProfitSheet> owList = scOutPortWarehouseGrossProfitSheetMapper.collectOW(vo);
        //获取6kg以上信息
//        List<ScOutPortWarehouseGrossProfitSheet> overList = scOutPortWarehouseGrossProfitSheetMapper.collectOverSix(vo);
        //获取偏远信息
        List<ScOutPortWarehouseGrossProfitSheet> remote = scOutPortWarehouseGrossProfitSheetMapper.collectRemote(vo);
        //获取偏远信息
//        List<ScOutPortWarehouseGrossProfitSheet> ln = scOutPortWarehouseGrossProfitSheetMapper.collectLN(vo);

        for (ScOutPortWarehouseGrossProfitSheet sheet : sheets) {
            for (ScOutPortWarehouseGrossProfitSheet scOutPortWarehouseGrossProfitSheet : scOutPortWarehouseGrossProfitSheets) {
                if (sheet != null && scOutPortWarehouseGrossProfitSheet != null &&
                        sheet.getContractArea() != null &&
                        sheet.getContractArea().equals(scOutPortWarehouseGrossProfitSheet.getContractArea())) {

                    sheet.setIwWeight(scOutPortWarehouseGrossProfitSheet.getIwWeight());
                    sheet.setIwStopLossWeight(scOutPortWarehouseGrossProfitSheet.getIwStopLossWeight());
                    sheet.setDailyAssessmentWeight(scOutPortWarehouseGrossProfitSheet.getDailyAssessmentWeight());
                    sheet.setIwDifference(scOutPortWarehouseGrossProfitSheet.getIwDifference());
                    sheet.setIwFee(scOutPortWarehouseGrossProfitSheet.getIwFee());
                    sheet.setIwParcelCount(scOutPortWarehouseGrossProfitSheet.getIwParcelCount());
                    sheet.setIwAverageWeight(scOutPortWarehouseGrossProfitSheet.getIwAverageWeight());
                    sheet.setIwAssessmentAverageWeight(scOutPortWarehouseGrossProfitSheet.getIwAssessmentAverageWeight());
                    sheet.setIwPerParcelFee(scOutPortWarehouseGrossProfitSheet.getIwPerParcelFee());
                    sheet.setIwPerKgFee(scOutPortWarehouseGrossProfitSheet.getIwPerKgFee());
                }
            }
            for (ScOutPortWarehouseGrossProfitSheet scOutPortWarehouseGrossProfitSheet : owList) {
                if (sheet != null && scOutPortWarehouseGrossProfitSheet != null &&
                        sheet.getContractArea() != null &&
                        scOutPortWarehouseGrossProfitSheet.getContractArea() != null &&
                        sheet.getContractArea().equals(scOutPortWarehouseGrossProfitSheet.getContractArea())) {

                    sheet.setOwWeight(scOutPortWarehouseGrossProfitSheet.getOwWeight());
                    sheet.setOwFee(scOutPortWarehouseGrossProfitSheet.getOwFee());
                    sheet.setOwParcelCount(scOutPortWarehouseGrossProfitSheet.getOwParcelCount());
                    sheet.setOwPerParcelFee(scOutPortWarehouseGrossProfitSheet.getOwPerParcelFee());
                    sheet.setOwPerKgFee(scOutPortWarehouseGrossProfitSheet.getOwPerKgFee());
                }
            }
//            for (ScOutPortWarehouseGrossProfitSheet scOutPortWarehouseGrossProfitSheet : overList) {
//                if (sheet.getContractArea().equals(scOutPortWarehouseGrossProfitSheet.getContractArea())) {
//                    sheet.setOverSixAmount(scOutPortWarehouseGrossProfitSheet.getOverSixAmount());
//                    sheet.setOverSixWeight(scOutPortWarehouseGrossProfitSheet.getOverSixWeight());
//                    sheet.setOverSixPerKgFee(scOutPortWarehouseGrossProfitSheet.getOverSixPerKgFee());
//                    sheet.setOverSixParcelCount(scOutPortWarehouseGrossProfitSheet.getOverSixParcelCount());
//                    sheet.setOverSixPerParcelFee(scOutPortWarehouseGrossProfitSheet.getOverSixPerParcelFee());
//                    sheet.setOverSixProportion(BigDecimal.valueOf(scOutPortWarehouseGrossProfitSheet.getOverSixParcelCount() / (sheet.getOwParcelCount() + sheet.getIwParcelCount())));
//                }
//            }
            for (ScOutPortWarehouseGrossProfitSheet scOutPortWarehouseGrossProfitSheet : remote) {
                if (sheet != null && scOutPortWarehouseGrossProfitSheet != null &&
                        sheet.getContractArea() != null &&
                        scOutPortWarehouseGrossProfitSheet.getContractArea() != null &&
                        sheet.getContractArea().equals(scOutPortWarehouseGrossProfitSheet.getContractArea())) {

                    sheet.setRemoteAreaAmount(scOutPortWarehouseGrossProfitSheet.getRemoteAreaAmount());
                    sheet.setRemoteAreaPerKgFee(scOutPortWarehouseGrossProfitSheet.getRemoteAreaPerKgFee());
                    sheet.setRemoteAreaWeight(scOutPortWarehouseGrossProfitSheet.getRemoteAreaWeight());
                    sheet.setRemoteAreaParcelCount(scOutPortWarehouseGrossProfitSheet.getRemoteAreaParcelCount());
                    sheet.setRemoteAreaPerParcelFee(scOutPortWarehouseGrossProfitSheet.getRemoteAreaPerParcelFee());
                    if (sheet.getOwParcelCount() + sheet.getIwParcelCount() == 0) {
                        sheet.setRemoteAreaProportion(new BigDecimal(0));
                    } else {
                        sheet.setRemoteAreaProportion(valueOf(scOutPortWarehouseGrossProfitSheet.getRemoteAreaParcelCount() / (sheet.getOwParcelCount() + sheet.getIwParcelCount())));
                    }
                    sheet.setOverSixAmount(scOutPortWarehouseGrossProfitSheet.getOverSixAmount());
                    sheet.setOverSixWeight(scOutPortWarehouseGrossProfitSheet.getOverSixWeight());
                    sheet.setOverSixPerKgFee(scOutPortWarehouseGrossProfitSheet.getOverSixPerKgFee());
                    sheet.setOverSixParcelCount(scOutPortWarehouseGrossProfitSheet.getOverSixParcelCount());
                    sheet.setOverSixPerParcelFee(scOutPortWarehouseGrossProfitSheet.getOverSixPerParcelFee());
                    if (sheet.getOwParcelCount() + sheet.getIwParcelCount() == 0) {
                        sheet.setOverSixProportion(valueOf(0));
                    } else {
                        sheet.setOverSixProportion(valueOf(scOutPortWarehouseGrossProfitSheet.getOverSixParcelCount() / (sheet.getOwParcelCount() + sheet.getIwParcelCount())));
                    }
                    sheet.setLnAmount(scOutPortWarehouseGrossProfitSheet.getLnAmount());
                    sheet.setLnWeight(scOutPortWarehouseGrossProfitSheet.getLnWeight());
                    sheet.setLnPerKgFee(scOutPortWarehouseGrossProfitSheet.getLnPerKgFee());
                    sheet.setLnParcelCount(scOutPortWarehouseGrossProfitSheet.getLnParcelCount());
                    sheet.setLnPerParcelFee(scOutPortWarehouseGrossProfitSheet.getLnPerParcelFee());
                    if (sheet.getOwParcelCount() + sheet.getIwParcelCount() == 0) {
                        sheet.setLnProportion(valueOf(0));
                    } else {
                        sheet.setLnProportion(valueOf(scOutPortWarehouseGrossProfitSheet.getLnParcelCount() / (sheet.getOwParcelCount() + sheet.getIwParcelCount())));
                    }
                    if (sheet.getLnProportion() == null) {
                        sheet.setLnProportion(new BigDecimal(0));
                    }
                    if (sheet.getRemoteAreaProportion() == null) {
                        sheet.setRemoteAreaProportion(new BigDecimal(0));
                    }
                }
            }
//            for (ScOutPortWarehouseGrossProfitSheet scOutPortWarehouseGrossProfitSheet : ln) {
//                if (sheet.getContractArea().equals(scOutPortWarehouseGrossProfitSheet.getContractArea())) {
//                    sheet.setLnAmount(scOutPortWarehouseGrossProfitSheet.getLnAmount());
//                    sheet.setLnWeight(scOutPortWarehouseGrossProfitSheet.getLnWeight());
//                    sheet.setLnPerKgFee(scOutPortWarehouseGrossProfitSheet.getLnPerKgFee());
//                    sheet.setLnParcelCount(scOutPortWarehouseGrossProfitSheet.getLnParcelCount());
//                    sheet.setLnPerParcelFee(scOutPortWarehouseGrossProfitSheet.getLnPerParcelFee());
//                    sheet.setLnProportion(BigDecimal.valueOf(scOutPortWarehouseGrossProfitSheet.getLnParcelCount() / (sheet.getOwParcelCount() + sheet.getIwParcelCount())));
//                    if (sheet.getLnProportion() == null) {
//                        sheet.setLnProportion(new BigDecimal(0));
//                    }
//                }
//            }
            // 初始化为默认值，比如0，以避免null
            BigDecimal iwWeight = sheet.getIwWeight() != null ? sheet.getIwWeight() : BigDecimal.ZERO;
            BigDecimal owWeight = sheet.getOwWeight() != null ? sheet.getOwWeight() : BigDecimal.ZERO;
            BigDecimal totalWeight = iwWeight.add(owWeight);
            sheet.setTotalWeight(totalWeight);

            BigDecimal transferFee = sheet.getTransferFee() != null ? sheet.getTransferFee() : BigDecimal.ZERO;
            BigDecimal fixedReturn = sheet.getFixedReturn() != null ? sheet.getFixedReturn() : BigDecimal.ZERO;
            BigDecimal actualTransferCost = transferFee.subtract(fixedReturn);
            sheet.setActualTransferCost(actualTransferCost);

            BigDecimal storageCharge = scOutPortWareDispositionVo.getStorageCharge() != null ? scOutPortWareDispositionVo.getStorageCharge() : BigDecimal.ZERO;
            sheet.setReceivableWarehouseFee(storageCharge);
            sheet.setActualOWFee(sheet.getOwFee() != null ? sheet.getOwFee() : BigDecimal.ZERO);

            BigDecimal accumulatedAmount = sheet.getAccumulatedAmount() != null ? sheet.getAccumulatedAmount() : BigDecimal.ZERO;
            BigDecimal warehouseFeeProfitAndLoss = storageCharge.subtract(accumulatedAmount);
            sheet.setWarehouseFeeProfitAndLoss(warehouseFeeProfitAndLoss);

            BigDecimal actualTransferCostNonNull = actualTransferCost.compareTo(BigDecimal.ZERO) != 0 ? actualTransferCost : BigDecimal.ZERO; // 避免除数为0
            BigDecimal transferFeeDeficit = sheet.getActualOWFee().subtract(actualTransferCostNonNull);
            sheet.setTransferFeeDeficit(transferFeeDeficit);

            BigDecimal totalProfitAndLoss = transferFeeDeficit.add(warehouseFeeProfitAndLoss);
            sheet.setTotalProfitAndLoss(totalProfitAndLoss);
        }*/
//        R r = this.collectNew(vo);
//        
//        JSONObject jsonObject = JSONObject.parseObject(JSONObject.toJSONString(r));
//        jsonObject.getJSONObject("date");

        Workbook workbook = new XSSFWorkbook(); // 创建一个新的Excel工作簿
        Sheet sheet = workbook.createSheet("Amount Data"); // 创建一个新的工作表
        String[] cellName = {"名称", "中转费", "包干费返还", "实际收取中转费", "包干费-日包干费",
                /*"包干费-累计金额",*/ "包仓内重量", /*"包仓内-止损重量",*/ "包仓内-日考核重量", "包仓内-重量差值",
                "包仓内费用", "包仓内票数", "包仓内-均重", "包仓内-考核均重", "包仓内-单票费用",
                "包仓内-单公斤费用", "包仓外重量", "包仓外费用", "包仓外票数", "包仓外-单票费用",
                "包仓外-单公斤费用", "包仓内6公斤以上-票数", "包仓内6公斤以上-重量", "包仓内6公斤以上-金额", "包仓内6公斤以上-单票费用", "包仓内6公斤以上-单公斤费用",
                "包仓内6公斤以上-占比", "偏远地区-票数", "偏远地区-重量", "偏远地区-金额", "偏远地区-单票费用",
                "偏远地区-单公斤费用", "偏远地区-占比", "辽宁-票数", "辽宁-重量", "辽宁-金额",
                "辽宁-单票费用", "辽宁-单公斤费用", "辽宁-占比", "总重量", "实际中转费",
                "包仓费", /*"包仓外中转费",*/ "包仓费盈亏", "中转费亏损", "中转考核", "合计盈亏"
        };
        if (vo.getNetworkLevel() != null && (vo.getNetworkLevel().equals("2") || vo.getNetworkLevel().equals("1"))) {
            cellName = new String[]{"名称", "中转费", "包干费返还", "实际收取中转费",
                    "包干费-日包干费",/*"包干费-累计金额",*/ "包仓内重量",
                    "包仓内费用", "包仓内票数", "包仓内-均重", "包仓内-考核均重", "包仓内-单票费用",
                    "包仓内-单公斤费用", "包仓外重量", "包仓外费用", "包仓外票数", "包仓外-单票费用",
                    "包仓外-单公斤费用", "包仓内6公斤以上-票数", "包仓内6公斤以上-重量", "包仓内6公斤以上-金额", "包仓内6公斤以上-单票费用", "包仓内6公斤以上-单公斤费用",
                    "包仓内6公斤以上-占比", "偏远地区-票数", "偏远地区-重量", "偏远地区-金额", "偏远地区-单票费用",
                    "偏远地区-单公斤费用", "偏远地区-占比", "辽宁-票数", "辽宁-重量", "辽宁-金额",
                    "辽宁-单票费用", "辽宁-单公斤费用", "辽宁-占比", "总重量", "实际中转费",
                    "包仓费", /*"包仓外中转费",*/ "包仓费盈亏", "中转费亏损", "合计盈亏"
            };
        }
        // 创建边框样式
        CellStyle borderStyle = workbook.createCellStyle();
        BorderStyle thin = BorderStyle.THIN;
        short blackColorIndex = IndexedColors.BLACK.getIndex();
        borderStyle.setBorderTop(thin);
        borderStyle.setBorderBottom(thin);
        borderStyle.setBorderLeft(thin);
        borderStyle.setBorderRight(thin);
        borderStyle.setTopBorderColor(blackColorIndex);
        borderStyle.setBottomBorderColor(blackColorIndex);
        borderStyle.setLeftBorderColor(blackColorIndex);
        borderStyle.setRightBorderColor(blackColorIndex);

// 创建表头样式
        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerFont.setFontHeightInPoints((short) 14); // 设置字体大小
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平居中
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置垂直居中
        headerStyle.setBorderTop(thin);
        headerStyle.setBorderBottom(thin);
        headerStyle.setBorderLeft(thin);
        headerStyle.setBorderRight(thin);
        headerStyle.setTopBorderColor(blackColorIndex);
        headerStyle.setBottomBorderColor(blackColorIndex);
        headerStyle.setLeftBorderColor(blackColorIndex);
        headerStyle.setRightBorderColor(blackColorIndex);
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < cellName.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(cellName[i]);
            cell.setCellStyle(headerStyle);
        }
        int rowNum = 1; // 行索引从1开始（因为已经有表头行）

        for (ScOutPortWarehouseGrossProfitSheet grossProfitSheet : list) {
            int c = 0;
            Row dataRow = sheet.createRow(rowNum++);
            // 承包区名称
            Cell cell0 = dataRow.createCell(c++);
            cell0.setCellStyle(borderStyle);
            if (grossProfitSheet.getContractArea() != null) {
                cell0.setCellValue(grossProfitSheet.getContractArea());
            }
            // 中转费
            Cell cell1 = dataRow.createCell(c++);
            cell1.setCellStyle(borderStyle);
            cell1.setCellValue(grossProfitSheet.getTransferFee() != null ? grossProfitSheet.getTransferFee().doubleValue() : 0.0);

// 包干费返还
            Cell cell2 = dataRow.createCell(c++);
            cell2.setCellStyle(borderStyle);
            cell2.setCellValue(grossProfitSheet.getFixedReturn() != null ? grossProfitSheet.getFixedReturn().doubleValue() : 0.0);

// 实际收取中转费
            Cell cell3 = dataRow.createCell(c++);
            cell3.setCellStyle(borderStyle);
            cell3.setCellValue(grossProfitSheet.getActualCharges() != null ? grossProfitSheet.getActualCharges().doubleValue() : 0.0);
            if (vo.getNetworkLevel() != null && (vo.getNetworkLevel().equals("2") || vo.getNetworkLevel().equals("1"))) {

// 累计金额
                Cell cell4 = dataRow.createCell(c++);
                cell4.setCellStyle(borderStyle);
                cell4.setCellValue(grossProfitSheet.getAccumulatedAmount() != null ? grossProfitSheet.getAccumulatedAmount().doubleValue() : 0.0);
// 包仓内相关属性
                Cell cell5 = dataRow.createCell(c++);
                cell5.setCellStyle(borderStyle);
                cell5.setCellValue(grossProfitSheet.getIwWeight() != null ? grossProfitSheet.getIwWeight().doubleValue() : 0.0);

                Cell cell6 = dataRow.createCell(c++);
                cell6.setCellStyle(borderStyle);
                cell6.setCellValue(grossProfitSheet.getIwFee() != null ? grossProfitSheet.getIwFee().doubleValue() : 0.0);

                Cell cell7 = dataRow.createCell(c++);
                cell7.setCellStyle(borderStyle);
                cell7.setCellValue(grossProfitSheet.getIwParcelCount() != null ? grossProfitSheet.getIwParcelCount() : 0);

                Cell cell8 = dataRow.createCell(c++);
                cell8.setCellStyle(borderStyle);
                cell8.setCellValue(grossProfitSheet.getIwAverageWeight() != null ? grossProfitSheet.getIwAverageWeight().doubleValue() : 0.0);

                Cell cell9 = dataRow.createCell(c++);
                cell9.setCellStyle(borderStyle);
                cell9.setCellValue(grossProfitSheet.getIwAssessmentAverageWeight() != null ? grossProfitSheet.getIwAssessmentAverageWeight().doubleValue() : 0.0);

                Cell cell10 = dataRow.createCell(c++);
                cell10.setCellStyle(borderStyle);
                cell10.setCellValue(grossProfitSheet.getIwPerParcelFee() != null ? grossProfitSheet.getIwPerParcelFee().doubleValue() : 0.0);

                Cell cell11 = dataRow.createCell(c++);
                cell11.setCellStyle(borderStyle);
                cell11.setCellValue(grossProfitSheet.getIwPerKgFee() != null ? grossProfitSheet.getIwPerKgFee().doubleValue() : 0.0);

                // 包仓外相关属性
                // 包仓外相关属性

                Cell cell12 = dataRow.createCell(c++);
                cell12.setCellStyle(borderStyle);
                cell12.setCellValue(grossProfitSheet.getOwWeight() != null ? grossProfitSheet.getOwWeight().doubleValue() : 0.0);

                Cell cell13 = dataRow.createCell(c++);
                cell13.setCellStyle(borderStyle);
                cell13.setCellValue(grossProfitSheet.getOwFee() != null ? grossProfitSheet.getOwFee().doubleValue() : 0.0);

                Cell cell14 = dataRow.createCell(c++);
                cell14.setCellStyle(borderStyle);
                cell14.setCellValue(grossProfitSheet.getOwParcelCount() != null ? grossProfitSheet.getOwParcelCount() : 0);

                Cell cell15 = dataRow.createCell(c++);
                cell15.setCellStyle(borderStyle);
                cell15.setCellValue(grossProfitSheet.getOwPerParcelFee() != null ? grossProfitSheet.getOwPerParcelFee().doubleValue() : 0.0);

                Cell cell16 = dataRow.createCell(c++);
                cell16.setCellStyle(borderStyle);
                cell16.setCellValue(grossProfitSheet.getOwPerKgFee() != null ? grossProfitSheet.getOwPerKgFee().doubleValue() : 0.0);
// 6公斤以上相关属性
                Cell cell17 = dataRow.createCell(c++);
                cell17.setCellStyle(borderStyle);
                cell17.setCellValue(grossProfitSheet.getOverSixParcelCount() != null ? grossProfitSheet.getOverSixParcelCount() : 0);

                Cell cell18 = dataRow.createCell(c++);
                cell18.setCellStyle(borderStyle);
                cell18.setCellValue(grossProfitSheet.getOverSixWeight() != null ? grossProfitSheet.getOverSixWeight().doubleValue() : 0.0);

                Cell cell19 = dataRow.createCell(c++);
                cell19.setCellStyle(borderStyle);
                cell19.setCellValue(grossProfitSheet.getOverSixAmount() != null ? grossProfitSheet.getOverSixAmount().doubleValue() : 0.0);

                Cell cell20 = dataRow.createCell(c++);
                cell20.setCellStyle(borderStyle);
                cell20.setCellValue(grossProfitSheet.getOverSixPerParcelFee() != null ? grossProfitSheet.getOverSixPerParcelFee().doubleValue() : 0.0);

                Cell cell21 = dataRow.createCell(c++);
                cell21.setCellStyle(borderStyle);
                cell21.setCellValue(grossProfitSheet.getOverSixPerKgFee() != null ? grossProfitSheet.getOverSixPerKgFee().doubleValue() : 0.0);

                Cell cell22 = dataRow.createCell(c++);
                cell22.setCellStyle(borderStyle);
                cell22.setCellValue(grossProfitSheet.getOverSixProportion() != null ? grossProfitSheet.getOverSixProportion().doubleValue() : 0.0);
// 偏远地区相关属性
                Cell cell23 = dataRow.createCell(c++);
                cell23.setCellStyle(borderStyle);
                cell23.setCellValue(grossProfitSheet.getRemoteAreaParcelCount() != null ? grossProfitSheet.getRemoteAreaParcelCount() : 0);

                Cell cell24 = dataRow.createCell(c++);
                cell24.setCellStyle(borderStyle);
                cell24.setCellValue(grossProfitSheet.getRemoteAreaWeight() != null ? grossProfitSheet.getRemoteAreaWeight().doubleValue() : 0.0);

                Cell cell25 = dataRow.createCell(c++);
                cell25.setCellStyle(borderStyle);
                cell25.setCellValue(grossProfitSheet.getRemoteAreaAmount() != null ? grossProfitSheet.getRemoteAreaAmount().doubleValue() : 0.0);

                Cell cell26 = dataRow.createCell(c++);
                cell26.setCellStyle(borderStyle);
                cell26.setCellValue(grossProfitSheet.getRemoteAreaPerParcelFee() != null ? grossProfitSheet.getRemoteAreaPerParcelFee().doubleValue() : 0.0);

                Cell cell27 = dataRow.createCell(c++);
                cell27.setCellStyle(borderStyle);
                cell27.setCellValue(grossProfitSheet.getRemoteAreaPerKgFee() != null ? grossProfitSheet.getRemoteAreaPerKgFee().doubleValue() : 0.0);

                Cell cell28 = dataRow.createCell(c++);
                cell28.setCellStyle(borderStyle);
                cell28.setCellValue(grossProfitSheet.getRemoteAreaProportion() != null ? grossProfitSheet.getRemoteAreaProportion().doubleValue() : 0.0);
// 辽宁相关属性
                Cell cell29 = dataRow.createCell(c++);
                cell29.setCellStyle(borderStyle);
                cell29.setCellValue(grossProfitSheet.getLnParcelCount() != null ? grossProfitSheet.getLnParcelCount() : 0);

                Cell cell30 = dataRow.createCell(c++);
                cell30.setCellStyle(borderStyle);
                cell30.setCellValue(grossProfitSheet.getLnWeight() != null ? grossProfitSheet.getLnWeight().doubleValue() : 0.0);

                Cell cell31 = dataRow.createCell(c++);
                cell31.setCellStyle(borderStyle);
                cell31.setCellValue(grossProfitSheet.getLnAmount() != null ? grossProfitSheet.getLnAmount().doubleValue() : 0.0);

                Cell cell32 = dataRow.createCell(c++);
                cell32.setCellStyle(borderStyle);
                cell32.setCellValue(grossProfitSheet.getLnPerParcelFee() != null ? grossProfitSheet.getLnPerParcelFee().doubleValue() : 0.0);

                Cell cell33 = dataRow.createCell(c++);
                cell33.setCellStyle(borderStyle);
                cell33.setCellValue(grossProfitSheet.getLnPerKgFee() != null ? grossProfitSheet.getLnPerKgFee().doubleValue() : 0.0);

                Cell cell34 = dataRow.createCell(c++);
                cell34.setCellStyle(borderStyle);
                cell34.setCellValue(grossProfitSheet.getLnProportion() != null ? grossProfitSheet.getLnProportion().doubleValue() : 0.0);

                Cell cell35 = dataRow.createCell(c++);
                cell35.setCellStyle(borderStyle);
                cell35.setCellValue(grossProfitSheet.getTotalWeight() != null ? grossProfitSheet.getTotalWeight().doubleValue() : 0.0);
// 实际中转费
                Cell cell36 = dataRow.createCell(c++);
                cell36.setCellStyle(borderStyle);
                cell36.setCellValue(grossProfitSheet.getActualTransferCost() != null ? grossProfitSheet.getActualTransferCost().doubleValue() : 0.0);

// 包仓费
                Cell cell37 = dataRow.createCell(c++);
                cell37.setCellStyle(borderStyle);
                cell37.setCellValue(grossProfitSheet.getReceivableWarehouseFee() != null ? grossProfitSheet.getReceivableWarehouseFee().doubleValue() : 0.0);

// 包仓外中转费
                /*Cell cell38 = dataRow.createCell(c++);
                cell38.setCellStyle(borderStyle);
                cell38.setCellValue(grossProfitSheet.getActualOWFee() != null ? grossProfitSheet.getActualOWFee().doubleValue() : 0.0);*/

// 包仓费盈亏
                Cell cell39 = dataRow.createCell(c++);
                cell39.setCellStyle(borderStyle);
                cell39.setCellValue(grossProfitSheet.getWarehouseFeeProfitAndLoss() != null ? grossProfitSheet.getWarehouseFeeProfitAndLoss().doubleValue() : 0.0);

// 中转费亏损
                Cell cell40 = dataRow.createCell(c++);
                cell40.setCellStyle(borderStyle);
                cell40.setCellValue(grossProfitSheet.getTransferFeeDeficit() != null ? grossProfitSheet.getTransferFeeDeficit().doubleValue() : 0.0);

// 合计盈亏
                Cell cell41 = dataRow.createCell(c++);
                cell41.setCellStyle(borderStyle);
                cell41.setCellValue(grossProfitSheet.getTotalProfitAndLoss() != null ? grossProfitSheet.getTotalProfitAndLoss().doubleValue() : 0.0);
            } else {
// 日包干费

                Cell cell4 = dataRow.createCell(c++);
                cell4.setCellStyle(borderStyle);
                cell4.setCellValue(grossProfitSheet.getFixedFee() != null ? grossProfitSheet.getFixedFee().doubleValue() : 0.0);

// 累计金额
                /*Cell cell5 = dataRow.createCell(c++);
                cell5.setCellStyle(borderStyle);
                cell5.setCellValue(grossProfitSheet.getAccumulatedAmount() != null ? grossProfitSheet.getAccumulatedAmount().doubleValue() : 0.0);*/

// 包仓内相关属性
                Cell cell6 = dataRow.createCell(c++);
                cell6.setCellStyle(borderStyle);
                cell6.setCellValue(grossProfitSheet.getIwWeight() != null ? grossProfitSheet.getIwWeight().doubleValue() : 0.0);

                /*Cell cell7 = dataRow.createCell(7);
                cell7.setCellStyle(borderStyle);
                cell7.setCellValue(grossProfitSheet.getIwStopLossWeight() != null ? grossProfitSheet.getIwStopLossWeight().doubleValue() : 0.0);*/

                Cell cell8 = dataRow.createCell(c++);
                cell8.setCellStyle(borderStyle);
                cell8.setCellValue(grossProfitSheet.getDailyAssessmentWeight() != null ? grossProfitSheet.getDailyAssessmentWeight().doubleValue() : 0.0);

                Cell cell9 = dataRow.createCell(c++);
                cell9.setCellStyle(borderStyle);
                cell9.setCellValue(grossProfitSheet.getIwDifference() != null ? grossProfitSheet.getIwDifference().doubleValue() : 0.0);

                Cell cell10 = dataRow.createCell(c++);
                cell10.setCellStyle(borderStyle);
                cell10.setCellValue(grossProfitSheet.getIwFee() != null ? grossProfitSheet.getIwFee().doubleValue() : 0.0);

                Cell cell11 = dataRow.createCell(c++);
                cell11.setCellStyle(borderStyle);
                cell11.setCellValue(grossProfitSheet.getIwParcelCount() != null ? grossProfitSheet.getIwParcelCount() : 0);

                Cell cell12 = dataRow.createCell(c++);
                cell12.setCellStyle(borderStyle);
                cell12.setCellValue(grossProfitSheet.getIwAverageWeight() != null ? grossProfitSheet.getIwAverageWeight().doubleValue() : 0.0);

                Cell cell13 = dataRow.createCell(c++);
                cell13.setCellStyle(borderStyle);
                cell13.setCellValue(grossProfitSheet.getIwAssessmentAverageWeight() != null ? grossProfitSheet.getIwAssessmentAverageWeight().doubleValue() : 0.0);

                Cell cell14 = dataRow.createCell(c++);
                cell14.setCellStyle(borderStyle);
                cell14.setCellValue(grossProfitSheet.getIwPerParcelFee() != null ? grossProfitSheet.getIwPerParcelFee().doubleValue() : 0.0);

                Cell cell15 = dataRow.createCell(c++);
                cell15.setCellStyle(borderStyle);
                cell15.setCellValue(grossProfitSheet.getIwPerKgFee() != null ? grossProfitSheet.getIwPerKgFee().doubleValue() : 0.0);


                // 包仓外相关属性
                // 包仓外相关属性
                Cell cell16 = dataRow.createCell(c++);
                cell16.setCellStyle(borderStyle);
                cell16.setCellValue(grossProfitSheet.getOwWeight() != null ? grossProfitSheet.getOwWeight().doubleValue() : 0.0);

                Cell cell17 = dataRow.createCell(c++);
                cell17.setCellStyle(borderStyle);
                cell17.setCellValue(grossProfitSheet.getOwFee() != null ? grossProfitSheet.getOwFee().doubleValue() : 0.0);

                Cell cell18 = dataRow.createCell(c++);
                cell18.setCellStyle(borderStyle);
                cell18.setCellValue(grossProfitSheet.getOwParcelCount() != null ? grossProfitSheet.getOwParcelCount() : 0);

                Cell cell19 = dataRow.createCell(c++);
                cell19.setCellStyle(borderStyle);
                cell19.setCellValue(grossProfitSheet.getOwPerParcelFee() != null ? grossProfitSheet.getOwPerParcelFee().doubleValue() : 0.0);

                Cell cell20 = dataRow.createCell(c++);
                cell20.setCellStyle(borderStyle);
                cell20.setCellValue(grossProfitSheet.getOwPerKgFee() != null ? grossProfitSheet.getOwPerKgFee().doubleValue() : 0.0);


// 6公斤以上相关属性
                Cell cell21 = dataRow.createCell(c++);
                cell21.setCellStyle(borderStyle);
                cell21.setCellValue(grossProfitSheet.getOverSixParcelCount() != null ? grossProfitSheet.getOverSixParcelCount() : 0);

                Cell cell22 = dataRow.createCell(c++);
                cell22.setCellStyle(borderStyle);
                cell22.setCellValue(grossProfitSheet.getOverSixWeight() != null ? grossProfitSheet.getOverSixWeight().doubleValue() : 0.0);

                Cell cell23 = dataRow.createCell(c++);
                cell23.setCellStyle(borderStyle);
                cell23.setCellValue(grossProfitSheet.getOverSixAmount() != null ? grossProfitSheet.getOverSixAmount().doubleValue() : 0.0);

                Cell cell24 = dataRow.createCell(c++);
                cell24.setCellStyle(borderStyle);
                cell24.setCellValue(grossProfitSheet.getOverSixPerParcelFee() != null ? grossProfitSheet.getOverSixPerParcelFee().doubleValue() : 0.0);

                Cell cell25 = dataRow.createCell(c++);
                cell25.setCellStyle(borderStyle);
                cell25.setCellValue(grossProfitSheet.getOverSixPerKgFee() != null ? grossProfitSheet.getOverSixPerKgFee().doubleValue() : 0.0);

                Cell cell26 = dataRow.createCell(c++);
                cell26.setCellStyle(borderStyle);
                cell26.setCellValue(grossProfitSheet.getOverSixProportion() != null ? grossProfitSheet.getOverSixProportion().doubleValue() : 0.0);
// 偏远地区相关属性
                Cell cell27 = dataRow.createCell(c++);
                cell27.setCellStyle(borderStyle);
                cell27.setCellValue(grossProfitSheet.getRemoteAreaParcelCount() != null ? grossProfitSheet.getRemoteAreaParcelCount() : 0);

                Cell cell28 = dataRow.createCell(c++);
                cell28.setCellStyle(borderStyle);
                cell28.setCellValue(grossProfitSheet.getRemoteAreaWeight() != null ? grossProfitSheet.getRemoteAreaWeight().doubleValue() : 0.0);

                Cell cell29 = dataRow.createCell(c++);
                cell29.setCellStyle(borderStyle);
                cell29.setCellValue(grossProfitSheet.getRemoteAreaAmount() != null ? grossProfitSheet.getRemoteAreaAmount().doubleValue() : 0.0);

                Cell cell30 = dataRow.createCell(c++);
                cell30.setCellStyle(borderStyle);
                cell30.setCellValue(grossProfitSheet.getRemoteAreaPerParcelFee() != null ? grossProfitSheet.getRemoteAreaPerParcelFee().doubleValue() : 0.0);

                Cell cell31 = dataRow.createCell(c++);
                cell31.setCellStyle(borderStyle);
                cell31.setCellValue(grossProfitSheet.getRemoteAreaPerKgFee() != null ? grossProfitSheet.getRemoteAreaPerKgFee().doubleValue() : 0.0);

                Cell cell32 = dataRow.createCell(c++);
                cell32.setCellStyle(borderStyle);
                cell32.setCellValue(grossProfitSheet.getRemoteAreaProportion() != null ? grossProfitSheet.getRemoteAreaProportion().doubleValue() : 0.0);
// 辽宁相关属性
                Cell cell33 = dataRow.createCell(c++);
                cell33.setCellStyle(borderStyle);
                cell33.setCellValue(grossProfitSheet.getLnParcelCount() != null ? grossProfitSheet.getLnParcelCount() : 0);

                Cell cell34 = dataRow.createCell(c++);
                cell34.setCellStyle(borderStyle);
                cell34.setCellValue(grossProfitSheet.getLnWeight() != null ? grossProfitSheet.getLnWeight().doubleValue() : 0.0);

                Cell cell35 = dataRow.createCell(c++);
                cell35.setCellStyle(borderStyle);
                cell35.setCellValue(grossProfitSheet.getLnAmount() != null ? grossProfitSheet.getLnAmount().doubleValue() : 0.0);

                Cell cell36 = dataRow.createCell(c++);
                cell36.setCellStyle(borderStyle);
                cell36.setCellValue(grossProfitSheet.getLnPerParcelFee() != null ? grossProfitSheet.getLnPerParcelFee().doubleValue() : 0.0);

                Cell cell37 = dataRow.createCell(c++);
                cell37.setCellStyle(borderStyle);
                cell37.setCellValue(grossProfitSheet.getLnPerKgFee() != null ? grossProfitSheet.getLnPerKgFee().doubleValue() : 0.0);

                Cell cell38 = dataRow.createCell(c++);
                cell38.setCellStyle(borderStyle);
                cell38.setCellValue(grossProfitSheet.getLnProportion() != null ? grossProfitSheet.getLnProportion().doubleValue() : 0.0);

                Cell cell39 = dataRow.createCell(c++);
                cell39.setCellStyle(borderStyle);
                cell39.setCellValue(grossProfitSheet.getTotalWeight() != null ? grossProfitSheet.getTotalWeight().doubleValue() : 0.0);

// 实际中转费
                Cell cell40 = dataRow.createCell(c++);
                cell40.setCellStyle(borderStyle);
                cell40.setCellValue(grossProfitSheet.getActualTransferCost() != null ? grossProfitSheet.getActualTransferCost().doubleValue() : 0.0);

// 包仓费
                Cell cell41 = dataRow.createCell(c++);
                cell41.setCellStyle(borderStyle);
                cell41.setCellValue(grossProfitSheet.getReceivableWarehouseFee() != null ? grossProfitSheet.getReceivableWarehouseFee().doubleValue() : 0.0);

// 包仓外中转费
                /*Cell cell42 = dataRow.createCell(c++);
                cell42.setCellStyle(borderStyle);
                cell42.setCellValue(grossProfitSheet.getActualOWFee() != null ? grossProfitSheet.getActualOWFee().doubleValue() : 0.0);*/

// 包仓费盈亏
                Cell cell43 = dataRow.createCell(c++);
                cell43.setCellStyle(borderStyle);
                cell43.setCellValue(grossProfitSheet.getWarehouseFeeProfitAndLoss() != null ? grossProfitSheet.getWarehouseFeeProfitAndLoss().doubleValue() : 0.0);

// 中转费亏损
                Cell cell44 = dataRow.createCell(c++);
                cell44.setCellStyle(borderStyle);
                cell44.setCellValue(grossProfitSheet.getTransferFeeDeficit() != null ? grossProfitSheet.getTransferFeeDeficit().doubleValue() : 0.0);
// transferExamine
                Cell cell45 = dataRow.createCell(c++);
                cell45.setCellStyle(borderStyle);
                cell45.setCellValue(grossProfitSheet.getTransferExamine() != null ? grossProfitSheet.getTransferExamine().doubleValue() : 0.0);

// 合计盈亏
                Cell cell46 = dataRow.createCell(c++);
                cell46.setCellStyle(borderStyle);
                cell46.setCellValue(grossProfitSheet.getTotalProfitAndLoss() != null ? grossProfitSheet.getTotalProfitAndLoss().doubleValue() : 0.0);
            }
        }
        try {
            workbook.write(outputStream); // 将工作簿写入输出流
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            workbook.close(); // 关闭工作簿
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    @Transactional
    public R updateDisposition(ScOutPortWareDispositionVo vo) {
//        vo.setId("1");
//        return scOutPortWarehouseGrossProfitSheetMapper.updateDisposition(vo) > 0 ? R.ok("修改成功") : R.fail("修改失败");

        if (vo.getStartDate() == null) {
            return R.fail("开始时间不能为空");
        }
        ScOutPortWareDispositionVo scWeightFlowDirectionDispositionVo1 = scOutPortWarehouseGrossProfitSheetMapper.selectDispositionByCreate(vo.getStartDate());
        if (scWeightFlowDirectionDispositionVo1 != null) {
            vo.setId(scWeightFlowDirectionDispositionVo1.getId());
            /*
            //承包区对应的包仓费配置，暂时注释
            ClientStorageChargeVo clientStorageChargeVo = scOutPortWarehouseGrossProfitSheetMapper.selectClientStorageChargeByDispositionIdAndClientCode(vo.getId(),vo.getClientCode());
            int i = 0;
            if (clientStorageChargeVo!=null){
                clientStorageChargeVo.setStorageCharge(vo.getStorageCharge());
                i = scOutPortWarehouseGrossProfitSheetMapper.updateClientStorageChargeById(clientStorageChargeVo);
            }else {
                clientStorageChargeVo = new ClientStorageChargeVo();
                clientStorageChargeVo.setDispositionId(vo.getId());
                clientStorageChargeVo.setClientCode(vo.getClientCode());
                clientStorageChargeVo.setStorageCharge(vo.getStorageCharge());
                i=scOutPortWarehouseGrossProfitSheetMapper.insertClientStorageCharge(clientStorageChargeVo);
            }
            if (i==0){
                return R.fail("保存配置失败");
            }*/
            return scOutPortWarehouseGrossProfitSheetMapper.updateDisposition(vo) > 0 ? R.ok("修改成功") : R.fail("修改失败");
        }

        ScOutPortWareDispositionVo scWeightFlowDirectionDispositionVo2 = scOutPortWarehouseGrossProfitSheetMapper.selectDisposition();
        Date startDate = vo.getStartDate();
        // 创建一个Calendar实例并设置为当前日期
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(startDate);

        // 从当前日期减去一天
        calendar.add(Calendar.DAY_OF_MONTH, -1);
        // 获取减去一天后的日期
        Date previousDate = calendar.getTime();

        scWeightFlowDirectionDispositionVo2.setEndDate(previousDate);
        scOutPortWarehouseGrossProfitSheetMapper.updateDisposition(scWeightFlowDirectionDispositionVo2);

        return scOutPortWarehouseGrossProfitSheetMapper.insertDisposition(vo) > 0 ? R.ok("修改成功") : R.fail("修改失败");
    }

    @Override
    public R<ScOutPortWareDispositionCloentVo> selectDisposition() {
        ScOutPortWareDispositionVo scOutPortWareDispositionVo = scOutPortWarehouseGrossProfitSheetMapper.selectDisposition();
        ScOutPortWareDispositionCloentVo scOutPortWareDispositionCloentVo = new ScOutPortWareDispositionCloentVo();
        BeanUtil.copyProperties(scOutPortWareDispositionVo, scOutPortWareDispositionCloentVo);
        List<ClientStorageChargeVo> storageChargeVos = scOutPortWarehouseGrossProfitSheetMapper.selectClientStorageChargeVo(scOutPortWareDispositionCloentVo.getId());
        scOutPortWareDispositionCloentVo.setStorageChargeVos(storageChargeVos);
        return R.ok(scOutPortWareDispositionCloentVo);
    }

    @Override
    public List<HqNetworkBillDetail> point(ScOutPortWarehouseGrossProfitSheetPointVo vo) {
        PageHelper.startPage(vo.getPageDate());
        return hqNetworkBillDetailMapper.outPortWarehousePoint(vo);
    }


    public List<TableVo> getTableVo(String networkLevel) {

        //一级标题
        List<TableVo> first = new ArrayList<>();
        TableVo firstTableVo = new TableVo();
        firstTableVo.setFields("collectDate");
        firstTableVo.setTranslate("日期");
        first.add(firstTableVo);

        firstTableVo = new TableVo();
        firstTableVo.setFields("contractArea");
        firstTableVo.setTranslate("承包区名称");
        first.add(firstTableVo);

        //应付总部金额
        firstTableVo = new TableVo();
        firstTableVo.setFields("headquarters");
        firstTableVo.setTranslate("应付总部金额");
        //应付总部金额-中转费金额
        List<TableVo> second = new ArrayList<>();
        TableVo secondTableVo = new TableVo();
        secondTableVo.setFields("transferFee");
        secondTableVo.setTranslate("中转费金额");
        secondTableVo.setIsPoint(1);
        second.add(secondTableVo);
        //应付总部金额-包干费返还
        secondTableVo = new TableVo();
        secondTableVo.setFields("fixedReturn");
        secondTableVo.setTranslate("包干费返还");
        second.add(secondTableVo);
        //应付总部金额-实际收取中转费
        secondTableVo = new TableVo();
        secondTableVo.setFields("actualCharges");
        secondTableVo.setTranslate("实际收取中转费");
        second.add(secondTableVo);
        //应付总部金额-包干费
        secondTableVo = new TableVo();
        secondTableVo.setFields("fix");
        secondTableVo.setTranslate("包干费");
        //应付总部金额-包干费-日包干费

        List<TableVo> third = new ArrayList<>();
        TableVo thirdTableVo = new TableVo();
        if (networkLevel == null || !networkLevel.equals("2")) {
            thirdTableVo.setTranslate("日包干费");
            thirdTableVo.setFields("fixedFee");
            third.add(thirdTableVo);
        } else {
            thirdTableVo = new TableVo();
            thirdTableVo.setTranslate("累计金额");
            thirdTableVo.setFields("accumulatedAmount");
            third.add(thirdTableVo);
        }
        //应付总部金额-包干费-累计金额
        /*thirdTableVo = new TableVo();
        thirdTableVo.setTranslate("累计金额");
        thirdTableVo.setFields("accumulatedAmount");
        third.add(thirdTableVo);*/
        secondTableVo.setSunList(third);
        second.add(secondTableVo);
        //应付总部金额-包仓内
        secondTableVo = new TableVo();
        secondTableVo.setFields("iw");
        secondTableVo.setTranslate("包仓内");
        //应付总部金额-包仓内-sunList
        third = new ArrayList<>();
        String[] iw = {"iwWeight", /*"iwStopLossWeight",*/ "dailyAssessmentWeight", "iwDifference", "iwFee", "iwParcelCount", "iwAverageWeight",
                "iwAssessmentAverageWeight", "iwPerParcelFee", "iwPerKgFee"};

        if (networkLevel != null && (networkLevel.equals("2") || networkLevel.equals("1"))) {
            iw = new String[]{"iwWeight", "iwFee", "iwParcelCount", "iwAverageWeight",
                    "iwAssessmentAverageWeight", "iwPerParcelFee", "iwPerKgFee"};
        }
        InitTableVo initTableVo = new InitTableVo();
        initTableVo.setFields(iw);
        initTableVo.setCode("scOutPortWare");
        third = tableService.initTable(initTableVo);
        for (TableVo tableVo : third) {
            String fields = tableVo.getFields();
            switch (fields) {
                case "iwWeight":
                    tableVo.setIsPoint(3);
                    break;
                case "iwFee":
                    tableVo.setIsPoint(4);
                    break;
                case "iwParcelCount":
                    tableVo.setIsPoint(5);
                    break;
            }
        }
        secondTableVo.setSunList(third);
        second.add(secondTableVo);
        //应付总部金额-包仓外
        secondTableVo = new TableVo();
        secondTableVo.setFields("ow");
        secondTableVo.setTranslate("包仓外");
        //应付总部金额-包仓外-sunList
        third = new ArrayList<>();
        String[] ow = {"owWeight", "owFee", "owParcelCount", "owPerParcelFee", "owPerKgFee"};
        initTableVo = new InitTableVo();
        initTableVo.setFields(ow);
        initTableVo.setCode("scOutPortWare");
        third = tableService.initTable(initTableVo);
        secondTableVo.setSunList(third);
        second.add(secondTableVo);
        //应付总部金额-6kg以上
        secondTableVo = new TableVo();
        secondTableVo.setFields("over");
        secondTableVo.setTranslate("包仓内6kg以上");
        //应付总部金额-6kg以上-sunList
        third = new ArrayList<>();
        String[] over = {"overSixParcelCount", "overSixWeight", "overSixAmount", "overSixPerParcelFee", "overSixPerKgFee", "overSixProportion"};
        initTableVo = new InitTableVo();
        initTableVo.setFields(over);
        initTableVo.setCode("scOutPortWare");
        third = tableService.initTable(initTableVo);
        for (TableVo tableVo : third) {
            if ("overSixParcelCount".equals(tableVo.getFields())) {
                tableVo.setIsPoint(6);
                break; // 找到后立即退出循环
            }
        }
        secondTableVo.setSunList(third);
        second.add(secondTableVo);
        //应付总部金额-偏远
        secondTableVo = new TableVo();
        secondTableVo.setFields("remote");
        secondTableVo.setTranslate("偏远地区");
        //应付总部金额-偏远-sunList
        third = new ArrayList<>();
        String[] remote = {"remoteAreaParcelCount", "remoteAreaWeight", "remoteAreaAmount", "remoteAreaPerParcelFee", "remoteAreaPerKgFee", "remoteAreaProportion"};
        initTableVo = new InitTableVo();
        initTableVo.setFields(remote);
        initTableVo.setCode("scOutPortWare");
        third = tableService.initTable(initTableVo);
        for (TableVo tableVo : third) {
            if ("remoteAreaParcelCount".equals(tableVo.getFields())) {
                tableVo.setIsPoint(7);
                break; // 找到后立即退出循环
            }
        }
        secondTableVo.setSunList(third);
        second.add(secondTableVo);
        //应付总部金额-辽宁
        secondTableVo = new TableVo();
        secondTableVo.setFields("lN");
        secondTableVo.setTranslate("辽宁");
        //应付总部金额-辽宁-sunList
        third = new ArrayList<>();
        String[] lN = {"lnParcelCount", "lnWeight", "lnAmount", "lnPerParcelFee", "lnPerKgFee", "lnProportion"};
        initTableVo = new InitTableVo();
        initTableVo.setFields(lN);
        initTableVo.setCode("scOutPortWare");
        third = tableService.initTable(initTableVo);
        for (TableVo tableVo : third) {
            if ("lnParcelCount".equals(tableVo.getFields())) {
                tableVo.setIsPoint(8);
                break; // 找到后立即退出循环
            }
        }
        secondTableVo.setSunList(third);
        second.add(secondTableVo);
        //应付总部金额-总重量
        secondTableVo = new TableVo();
        secondTableVo.setFields("totalWeight");
        secondTableVo.setTranslate("总重量");
        second.add(secondTableVo);
        //应付总部金额-实际中转费
        secondTableVo = new TableVo();
        secondTableVo.setFields("actualTransferCost");
        secondTableVo.setTranslate("实际中转费");
        second.add(secondTableVo);
        firstTableVo.setSunList(second);
        first.add(firstTableVo);

        //应收承包区金额
        firstTableVo = new TableVo();
        firstTableVo.setFields("area");
        firstTableVo.setTranslate("应收承包区金额");
        second = new ArrayList<>();
        //应收承包区金额-包仓费
        secondTableVo = new TableVo();
        secondTableVo.setFields("receivableWarehouseFee");
        secondTableVo.setTranslate("包仓费");
        second.add(secondTableVo);
        //应收承包区金额-包仓外中转费
        /*secondTableVo = new TableVo();
        secondTableVo.setFields("actualOWFee");
        secondTableVo.setTranslate("包仓外中转费");
        second.add(secondTableVo);*/
        firstTableVo.setSunList(second);
        first.add(firstTableVo);

        //包仓中转毛利
        firstTableVo = new TableVo();
        firstTableVo.setFields("bc");
        firstTableVo.setTranslate("包仓中转毛利");
        second = new ArrayList<>();
        //包仓中转毛利-包仓费盈亏
        secondTableVo = new TableVo();
        secondTableVo.setFields("warehouseFeeProfitAndLoss");
        secondTableVo.setTranslate("包仓费盈亏");
        second.add(secondTableVo);
        //包仓中转毛利-中转费亏损
        secondTableVo = new TableVo();
        secondTableVo.setFields("transferFeeDeficit");
        secondTableVo.setTranslate("中转费亏损");
        second.add(secondTableVo);
        if (networkLevel.equals("0")) {
            //包仓中转毛利-中转考核
            secondTableVo = new TableVo();
            secondTableVo.setFields("transferExamine");
            secondTableVo.setTranslate("中转考核");
            second.add(secondTableVo);
        }
        //包仓中转毛利-合计盈亏
        secondTableVo = new TableVo();
        secondTableVo.setFields("totalProfitAndLoss");
        secondTableVo.setTranslate("合计盈亏");
        second.add(secondTableVo);
        firstTableVo.setSunList(second);
        first.add(firstTableVo);

        return first;
    }


    @Override
    public R collectNew(ScOutPortWarehouseGrossProfitSheetVo vo) {
        List<ScOutPortWarehouseGrossProfitSheet> list = collectNewCache(vo);
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("list", PageInfo.info(list));
        jsonObject.put("fields", getTableVo(vo.getNetworkLevel()));

        if (list == null || list.size() == 0) {
            return R.fail("暂无数据");
        } else {
            return R.ok(jsonObject);
        }
    }

    @Override
    public R collectCache(ScOutPortWarehouseGrossProfitSheetVo vo) {
        PageHelper.startPage(vo.getPageDate());
        List<ScOutPortWarehouseGrossProfitSheet> detailList = detailMapper.selectByAll(vo);
        if (!vo.getNetworkLevel().equals("0")) {
            vo.setNetworkLevel("0");
            List<ScOutPortWarehouseGrossProfitSheet> scOutPortWarehouseGrossProfitSheets = detailMapper.selectByAll(vo);
            BigDecimal transferFeeSum = new BigDecimal(0.000);
            BigDecimal fixedReturnSum = new BigDecimal(0.000);
            BigDecimal transferExamine = new BigDecimal(0.000);
            for (ScOutPortWarehouseGrossProfitSheet scOutPortWarehouseGrossProfitSheet : scOutPortWarehouseGrossProfitSheets) {
                transferFeeSum = transferFeeSum.add(scOutPortWarehouseGrossProfitSheet.getTransferFee());
                fixedReturnSum = fixedReturnSum.add(scOutPortWarehouseGrossProfitSheet.getFixedReturn());
                transferExamine = scOutPortWarehouseGrossProfitSheet.getTransferExamine();
            }
            BigDecimal rate = fixedReturnSum.divide(transferFeeSum, 10, ROUND_HALF_UP);
            for (ScOutPortWarehouseGrossProfitSheet scOutPortWarehouseGrossProfitSheet : detailList) {
                if (scOutPortWarehouseGrossProfitSheet.getTransferFee() != null) {
                    scOutPortWarehouseGrossProfitSheet.setFixedReturn(rate.multiply(scOutPortWarehouseGrossProfitSheet.getTransferFee()).setScale(2, RoundingMode.HALF_UP));
                    scOutPortWarehouseGrossProfitSheet.setActualCharges(scOutPortWarehouseGrossProfitSheet.getTransferFee().add(scOutPortWarehouseGrossProfitSheet.getFixedReturn()));
                    scOutPortWarehouseGrossProfitSheet.setActualTransferCost(scOutPortWarehouseGrossProfitSheet.getActualCharges().subtract(scOutPortWarehouseGrossProfitSheet.getFixedFee()));
                }
                if (scOutPortWarehouseGrossProfitSheet.getContractArea().equals("辽宁盘锦公司")){
                    scOutPortWarehouseGrossProfitSheet.setTransferExamine(transferExamine);
                }
                //包仓内日考核重量
                scOutPortWarehouseGrossProfitSheet.setDailyAssessmentWeight(new BigDecimal(0));
                scOutPortWarehouseGrossProfitSheet.setIwDifference(new BigDecimal(0));
            }
        }
        for (ScOutPortWarehouseGrossProfitSheet scOutPortWarehouseGrossProfitSheet : detailList) {
            scOutPortWarehouseGrossProfitSheet.setReceivableWarehouseFee(scOutPortWarehouseGrossProfitSheet.getReceivableWarehouseFee().setScale(0,RoundingMode.HALF_UP));
            scOutPortWarehouseGrossProfitSheet.setTransferFeeDeficit(scOutPortWarehouseGrossProfitSheet.getOwFee().subtract(scOutPortWarehouseGrossProfitSheet.getActualTransferCost()));
            if (scOutPortWarehouseGrossProfitSheet.getTransferFeeDeficit() == null) {
                scOutPortWarehouseGrossProfitSheet.setTransferFeeDeficit(new BigDecimal(0));
            }
            if (scOutPortWarehouseGrossProfitSheet.getTransferExamine() == null) {
                scOutPortWarehouseGrossProfitSheet.setTransferExamine(new BigDecimal(0));
            }
            scOutPortWarehouseGrossProfitSheet.setTotalProfitAndLoss(scOutPortWarehouseGrossProfitSheet.getWarehouseFeeProfitAndLoss()
                    .add(scOutPortWarehouseGrossProfitSheet.getTransferFeeDeficit().subtract(scOutPortWarehouseGrossProfitSheet.getTransferExamine())));
        }
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("list", PageInfo.info(detailList));
        jsonObject.put("fields", getTableVo(vo.getNetworkLevel()));
        if (detailList == null || detailList.size() == 0) {
            return R.fail("暂无数据");
        } else {
            return R.ok(jsonObject);
        }
    }

    private String addOneDayToDateString(String startDate) {
        // 定义日期格式
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");

        // 解析输入的字符串为LocalDate对象
        LocalDate date = LocalDate.parse(startDate, formatter);

        // 将日期加一天
        LocalDate newDate = date.plusDays(1);

        // 将新的日期转换回字符串
        return newDate.format(formatter);
    }


    public List<ScOutPortWarehouseGrossProfitSheet> collectNewCache(ScOutPortWarehouseGrossProfitSheetVo vo) {
        int time = Integer.parseInt(vo.getEndTime()) - Integer.parseInt(vo.getStartTime());
        if (time == 0) {
            time = 1;
        }
        int year = Integer.parseInt(vo.getStartTime().substring(0, 4));
        int month = Integer.parseInt(vo.getStartTime().substring(4, 6));
        int days = YearMonth.of(year, month).lengthOfMonth();

        List<ScOutBoundSendSheet> scOutBoundSendSheets = scOutBoundSendSheetMapper.selectAll();
        Map<String, BigDecimal> scOutBoundSendSheetMap = scOutBoundSendSheets.stream().collect(Collectors.toMap(a -> a.getContractArea(), a -> a.getFixedWarehousingFee()));

        BigDecimal scOutBoundSendSheetsSum = BigDecimal.valueOf(0.00);
        for (ScOutBoundSendSheet scOutBoundSendSheet : scOutBoundSendSheets) {
            scOutBoundSendSheetsSum = scOutBoundSendSheetsSum.add(scOutBoundSendSheet.getFixedWarehousingFee());
        }

        List<ScOutboundSendClientSet> scOutboundSendClientSets = scOutboundSendClientSetMapper.selectAll();
        Map<String, BigDecimal> scOutboundSendClientSetMap = scOutboundSendClientSets.stream().collect(Collectors.toMap(a -> a.getClientName(), a -> a.getFixedWarehousingFee() == null ? BigDecimal.valueOf(0) : a.getFixedWarehousingFee()));

        ScOutPortWareDispositionVo scOutPortWareDispositionVo = scOutPortWarehouseGrossProfitSheetMapper.selectDisposition();
        if (scOutPortWareDispositionVo.getStartDate()!=null){
            SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");
            try {
                Date date = formatter.parse(vo.getEndTime());
                if (date.after(scOutPortWareDispositionVo.getStartDate())){
                    BeanUtils.copyProperties(scOutPortWareDispositionVo,vo);
                }
            } catch (ParseException e) {
                throw new RuntimeException(e);
            }

        }
        PageHelper.startPage(vo.getPageDate());
        vo.setUpdateStartTime(addOneDayToDateString(vo.getStartTime()));
        vo.setUpdateEndTime(addOneDayToDateString(vo.getEndTime()));
        List<ScOutPortWarehouseGrossProfitSheet> list = scOutPortWarehouseGrossProfitMapper.collect(vo);
        BigDecimal selectTransferFee = scOutPortWarehouseGrossProfitMapper.transferFee(vo.getStartTime(), vo.getEndTime());
        BigDecimal rate = BigDecimal.valueOf(0.00);
        if (selectTransferFee.compareTo(BigDecimal.ZERO) != 0) {
            BigDecimal fixedReturn = scOutPortWarehouseGrossProfitMapper.selectFixedReturn(vo.getUpdateStartTime(), vo.getUpdateEndTime());
            rate = fixedReturn.divide(selectTransferFee,5,BigDecimal.ROUND_UP);
        }
        //计算总的中转费
        BigDecimal sumTransferFee = scOutPortWarehouseGrossProfitMapper.transferFee(vo.getStartTime(), vo.getEndTime());

        for (ScOutPortWarehouseGrossProfitSheet sheet : list) {
            sheet.setCollectDate(vo.getStartTime() + "至" + vo.getEndTime());
            BigDecimal total = BigDecimal.valueOf(sheet.getOwParcelCount() + sheet.getIwParcelCount());
            //包干费返还
            if (vo.getNetworkLevel().equals("1") || vo.getNetworkLevel().equals("2")) {
                // 计算固定回报并保留两位小数
//                BigDecimal fixedRate = BigDecimal.valueOf(0.00);
//                if (sheet.getTransferFee()!=null){
//                    fixedRate = sheet.getTransferFee().divide(selectTransferFee);
//                }
                BigDecimal fixedReturnCalculated = rate.multiply(sheet.getTransferFee()).setScale(3,RoundingMode.HALF_UP);
                sheet.setFixedReturn(fixedReturnCalculated);
                //包干费计算
                BigDecimal fixedFee = sheet.getFixedFee();
                if (fixedFee == null) {
                    fixedFee = BigDecimal.valueOf(0);
                }
                if (sumTransferFee == null) {
                    sumTransferFee = BigDecimal.valueOf(0);
                }
                BigDecimal transferFee = sheet.getTransferFee();
                if (transferFee == null) {
                    transferFee = BigDecimal.valueOf(0);
                }
                BigDecimal multiply = fixedFee.divide(sumTransferFee, 9, RoundingMode.HALF_UP).multiply(transferFee).setScale(3, RoundingMode.HALF_UP);
                sheet.setAccumulatedAmount(multiply);
                sheet.setFixedFee(multiply);
                sheet.setFixedReturn(sheet.getFixedReturn().setScale(3, RoundingMode.HALF_UP));
                if (sheet.getFixedReturn().compareTo(new BigDecimal(0)) > 0) {
                    sheet.setFixedReturn(new BigDecimal(0).subtract(sheet.getFixedReturn()));
                }
                if (vo.getNetworkLevel().equals("1")) {
                    sheet.setReceivableWarehouseFee(scOutBoundSendSheetMap.get(sheet.getContractArea()).divide(new BigDecimal(days), 3, RoundingMode.HALF_UP).multiply(new BigDecimal(time)));
                } else {
                    sheet.setReceivableWarehouseFee(scOutboundSendClientSetMap.get(sheet.getContractArea()).divide(new BigDecimal(days), 3, RoundingMode.HALF_UP).multiply(new BigDecimal(time)));
                }
            }
            if (vo.getNetworkLevel().equals("0")) {
                sheet.setReceivableWarehouseFee(scOutBoundSendSheetsSum.divide(new BigDecimal(days), 3, RoundingMode.HALF_UP).multiply(new BigDecimal(time)));
                sheet.setFixedReturn(sheet.getFixedReturn().setScale(3, RoundingMode.HALF_UP));
                if (sheet.getFixedReturn().compareTo(new BigDecimal(0)) > 0) {
                    sheet.setFixedReturn(new BigDecimal(0).subtract(sheet.getFixedReturn()));
                }
            }
            //计算-实际收取中转费
            BigDecimal actualChargesCalculated = sheet.getTransferFee()
                    .add(sheet.getFixedReturn())
                    .setScale(2, RoundingMode.HALF_UP);
            sheet.setActualCharges(actualChargesCalculated);

            //重量差值
            if (sheet.getIwWeight() != null && sheet.getIwStopLossWeight() != null) {
                sheet.setIwDifference(sheet.getIwWeight().subtract(sheet.getIwStopLossWeight()));
            }
            //包仓内-均重
            if (sheet.getIwParcelCount() != null && sheet.getIwParcelCount() != 0) {
                sheet.setIwAverageWeight(sheet.getIwWeight().divide(valueOf(sheet.getIwParcelCount()), mc));
            } else {
                sheet.setIwAverageWeight(ZERO);
            }
            //包仓内-单票费用
            if (sheet.getIwParcelCount() != null && sheet.getIwParcelCount() != 0) {
                sheet.setIwPerParcelFee(sheet.getIwFee().divide(valueOf(sheet.getIwParcelCount()), mc));
            } else {
                sheet.setIwPerParcelFee(ZERO);
            }
            //包仓内-单公斤费用
            if (sheet.getIwWeight() != null && sheet.getIwWeight().compareTo(BigDecimal.ZERO) != 0) {
                sheet.setIwPerKgFee(sheet.getIwFee().divide(sheet.getIwWeight(), mc));
            } else {
                sheet.setIwPerKgFee(ZERO);
            }
            sheet.setActualTransferCost(sheet.getActualCharges().subtract(sheet.getFixedFee()));

            //包仓外重量
            sheet.setOwWeight(sheet.getOverSixWeight().add(sheet.getRemoteAreaWeight()).add(sheet.getLnWeight()));
            //包仓外费用
            BigDecimal bigDecimal = scOutPortWarehouseGrossProfitSheetMapper.selectTransferFee(vo.getStartTime(), vo.getEndTime());
            sheet.setOwFee(bigDecimal);
            //包仓外票数
            sheet.setOwParcelCount(sheet.getOverSixParcelCount() + sheet.getRemoteAreaParcelCount() + sheet.getLnParcelCount());


            //包仓外-单票费用
            if (sheet.getOwParcelCount() != 0) {
                sheet.setOwPerParcelFee(sheet.getOwFee().divide(BigDecimal.valueOf(sheet.getOwParcelCount()), mc));
            }

            //包仓外-单公斤费用
            if (sheet.getOwWeight() != null && sheet.getOwWeight().compareTo(BigDecimal.ZERO) != 0) {
                sheet.setOwPerKgFee(sheet.getOwFee().divide(sheet.getOwWeight(), mc));
            } else {
                // 这里处理除数为0的情况，比如设置一个默认值或者记录日志等
                // 例如，可以设置一个默认费用或者将该字段置为null，具体取决于业务需求
                sheet.setOwPerKgFee(BigDecimal.ZERO); // 假设默认值为0
            }


            //6kg以上单票费用
            if (sheet.getOverSixParcelCount() != 0) {
                sheet.setOverSixPerParcelFee(sheet.getOverSixAmount().divide(BigDecimal.valueOf(sheet.getOverSixParcelCount()), mc));
            }
            //单公斤费用-
            if (sheet.getOverSixWeight() != null && sheet.getOverSixWeight().compareTo(BigDecimal.ZERO) != 0) {
                sheet.setOverSixPerKgFee(sheet.getOverSixAmount().divide(sheet.getOverSixWeight(), mc));
            } else {
                // 处理除数为0的情况，根据业务需求设定合理的默认值或行为
                sheet.setOverSixPerKgFee(BigDecimal.ZERO); // 假设默认值为0，根据实际情况调整
            }

//            占比
            if (total != null && total.compareTo(BigDecimal.ZERO) != 0) {
                sheet.setOverSixProportion(BigDecimal.valueOf(sheet.getOverSixParcelCount()).divide(total, mc));
            } else {
                sheet.setOverSixProportion(BigDecimal.ZERO);
            }

            //偏远地区单票费用
            if (sheet.getRemoteAreaParcelCount() != 0) {
                sheet.setRemoteAreaPerParcelFee(sheet.getRemoteAreaAmount().divide(BigDecimal.valueOf(sheet.getRemoteAreaParcelCount()), mc));
            }
            // 单公斤费用
            if (sheet.getRemoteAreaWeight() != null && sheet.getRemoteAreaPerParcelFee() != null) {
                sheet.setRemoteAreaPerKgFee(sheet.getRemoteAreaAmount().divide(sheet.getRemoteAreaWeight(), mc));
            }

            // 占比
            if (total != null && total.compareTo(BigDecimal.ZERO) != 0) {
                sheet.setRemoteAreaProportion(BigDecimal.valueOf(sheet.getRemoteAreaParcelCount()).divide(total, mc));
            } else {
                // 处理 total 为0的情况，这里可以根据业务需求设置一个默认值或者不设置该比例等
                // 例如，设置为0或者不设置（具体看业务场景）
                sheet.setRemoteAreaProportion(BigDecimal.ZERO); // 假设默认值为0，根据实际情况调整
            }

            //辽宁-单票费用
            if (sheet.getLnParcelCount() != 0) {
                sheet.setLnPerParcelFee(sheet.getLnAmount().divide(BigDecimal.valueOf(sheet.getLnParcelCount()), mc));
            }

            //单公斤费用
            if (sheet.getLnWeight() != null && sheet.getLnWeight().compareTo(BigDecimal.ZERO) != 0) {
                sheet.setLnPerKgFee(sheet.getLnAmount().divide(sheet.getLnWeight(), mc));
            } else {
                // 处理 sheet.getLnWeight() 为0的情况，根据业务需求决定如何处理
                // 可能是设置一个默认值，或者采取其他逻辑处理
                sheet.setLnPerKgFee(BigDecimal.ZERO); // 示例中设为0，根据实际情况调整
            }

            //占比
            if (total != null && total.compareTo(BigDecimal.ZERO) != 0) {
                sheet.setLnProportion(BigDecimal.valueOf(sheet.getLnParcelCount()).divide(total, mc));
            } else {
                // 处理 total 为0的情形，根据业务逻辑决定如何处理
                // 可能设置一个默认值、记录日志或者不执行任何操作
                sheet.setLnProportion(BigDecimal.ZERO); // 示例中将其设为0，实际根据需求调整
            }


            //总重量
            sheet.setTotalWeight(sheet.getOwWeight().add(sheet.getIwWeight()));
            // 实际中转费
            /*if (sheet.getFixedReturn() != null && sheet.getFixedFee() != null) {
                sheet.setActualTransferCost(sheet.getFixedReturn().subtract(sheet.getFixedFee()));
            }*/
            //sheet.setActualTransferCost(sheet.getActualCharges().subtract(sheet.getAccumulatedAmount()));

            //包仓外中转费
            sheet.setActualOWFee(sheet.getOwFee());

            //包仓费盈亏
            /*
            //原公式
            sheet.setWarehouseFeeProfitAndLoss(
                    (sheet.getReceivableWarehouseFee() != null ? sheet.getReceivableWarehouseFee() : BigDecimal.ZERO)
                            .subtract(sheet.getFixedFee() != null ? sheet.getFixedFee() : BigDecimal.ZERO)
            );*/
            sheet.setWarehouseFeeProfitAndLoss(
                    (sheet.getReceivableWarehouseFee() != null ? sheet.getReceivableWarehouseFee() : BigDecimal.ZERO)
                            .subtract(sheet.getFixedFee() != null ? sheet.getFixedFee() : BigDecimal.ZERO)
            );

            sheet.setTransferFeeDeficit(
                    (sheet.getActualOWFee() != null ? sheet.getActualOWFee() : BigDecimal.ZERO)
                            .subtract(sheet.getActualTransferCost() != null ? sheet.getActualTransferCost() : BigDecimal.ZERO)
            );
            if (vo.getNetworkLevel().equals("0")) {
                sheet.setTotalProfitAndLoss(
                        (sheet.getWarehouseFeeProfitAndLoss() != null ? sheet.getWarehouseFeeProfitAndLoss() : BigDecimal.ZERO)
                                .add(sheet.getTransferFeeDeficit() != null ? sheet.getTransferFeeDeficit() : BigDecimal.ZERO)
                                .subtract(sheet.getTransferExamine() != null ? sheet.getTransferExamine() : ZERO)
                );
            } else {
                sheet.setTotalProfitAndLoss(
                        (sheet.getWarehouseFeeProfitAndLoss() != null ? sheet.getWarehouseFeeProfitAndLoss() : BigDecimal.ZERO)
                                .add(sheet.getTransferFeeDeficit() != null ? sheet.getTransferFeeDeficit() : BigDecimal.ZERO)
                );
            }
            //包干费返还
            /*BigDecimal fixedFee = sheet.getFixedFee();
            if (fixedFee==null){
                fixedFee = BigDecimal.valueOf(0);
            }
            BigDecimal accumulatedAmount = sheet.getAccumulatedAmount();
            if (accumulatedAmount==null){
                accumulatedAmount = BigDecimal.valueOf(0);
            }
            BigDecimal transferFee = sheet.getTransferFee();
            if (transferFee==null){
                transferFee = BigDecimal.valueOf(0);
            }
            
            fixedFee.subtract(accumulatedAmount);*/

        }
        return list;
    }

    @Override
    public R cache(DateStrVo dateStrVo) {
        Integer[] networkLevels = {0, 1, 2};
        List<String> days = GetDateStringUtils.getDateStr(dateStrVo.getStartDateStr(), dateStrVo.getEndDateStr());
        List<String> dateStrList = detailMapper.selectllDateStr();
        days.removeAll(dateStrList);
        for (String day : days) {
            String time = day;
            for (Integer networkLevel : networkLevels) {
                ScOutPortWarehouseGrossProfitSheetVo scOutPortWarehouseGrossProfitSheetVo = new ScOutPortWarehouseGrossProfitSheetVo();
                scOutPortWarehouseGrossProfitSheetVo.setNetworkLevel(String.valueOf(networkLevel));
                scOutPortWarehouseGrossProfitSheetVo.setEndTime(time);
                scOutPortWarehouseGrossProfitSheetVo.setStartTime(time);
                PageDate pageDate = new PageDate();
                pageDate.setPageSize(100000);
                pageDate.setPageNum(1);
                scOutPortWarehouseGrossProfitSheetVo.setPageDate(pageDate);
                List<ScOutPortWarehouseGrossProfitSheet> scOutPortWarehouseGrossProfitSheets = collectNewCache(scOutPortWarehouseGrossProfitSheetVo);
                List<ScOutPortWarehouseGrossProfitSheetDetail> detailList = new ArrayList<>();
                for (ScOutPortWarehouseGrossProfitSheet scOutPortWarehouseGrossProfitSheet : scOutPortWarehouseGrossProfitSheets) {
                    ScOutPortWarehouseGrossProfitSheetDetail scOutPortWarehouseGrossProfitSheetDetail = new ScOutPortWarehouseGrossProfitSheetDetail();
                    BeanUtils.copyProperties(scOutPortWarehouseGrossProfitSheet, scOutPortWarehouseGrossProfitSheetDetail);
                    scOutPortWarehouseGrossProfitSheetDetail.setNetworkLevel(String.valueOf(networkLevel));
                    scOutPortWarehouseGrossProfitSheetDetail.setCollectDate(time);
                    detailList.add(scOutPortWarehouseGrossProfitSheetDetail);
                }
                if (detailList.size() == 0) {
                    ScOutPortWarehouseGrossProfitSheetDetail scOutPortWarehouseGrossProfitSheetDetail = new ScOutPortWarehouseGrossProfitSheetDetail();
                    scOutPortWarehouseGrossProfitSheetDetail.setNetworkLevel(String.valueOf(networkLevel));
                    scOutPortWarehouseGrossProfitSheetDetail.setCollectDate(time);
                    detailMapper.insert(scOutPortWarehouseGrossProfitSheetDetail);
                } else {
                    detailMapper.insertList(detailList);
                }

            }
            System.out.println(time + "的数据缓存成功");
        }

        return R.ok();
    }
}
